package com.lihui.dao;

import com.lihui.model.Student;
import com.lihui.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;

/**
 * 功能描述
 *
 * @author wileda
 * @date 2022/12/28  17:36
 */
public class StudentDao{
    public int insert(Student student){
        Connection connection = null;
        PreparedStatement statement = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql = "insert into student (sn,name,qq_mail,classes_id) values(?,?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1,student.getSn());
            statement.setString(2,student.getName());
            statement.setString(3, student.getMail());
            statement.setInt(4,student.getClassesId());
            result = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(null,statement,connection);
        }
        return result;
    }

    public List<Student> selectByName(String name){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Student> result = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from student where name = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,name);
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                if(result == null){
                    result = new ArrayList<>();
                }
                Student student = new Student();
                student.setId(resultSet.getInt(1));
                student.setSn(resultSet.getString(2));
                student.setName(resultSet.getString(3));
                student.setMail(resultSet.getString(4));
                student.setClassesId(resultSet.getInt(5));
                result.add(student);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }finally {
            DBUtil.close(resultSet,statement,connection);
        }


        return result;
    }

    public int updateClassesId(int studentId,int classesId){
        Connection connection = null;
        PreparedStatement statement = null;
        int result = 0;
        try {
            connection = DBUtil.getConnection();
            String sql = "update student set classes_id = ? where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,classesId);
            statement.setInt(2,studentId);
            result = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(null,statement,connection);
        }
        return result;
    }

    public List<Student> selectById(int classes_id){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Student> result = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from student where classes_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,classes_id);
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                if(result == null){
                    result = new ArrayList<>();
                }
                Student student = new Student();
                student.setId(resultSet.getInt(1));
                student.setSn(resultSet.getString(2));
                student.setName(resultSet.getString(3));
                student.setMail(resultSet.getString(4));
                student.setClassesId(resultSet.getInt(5));
                result.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(resultSet,statement,connection);
        }
        return result;
    }

    public int deleteById(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql = "delete from student where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            result = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(null,statement,connection);
        }
        return result;
    }
}
